﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using CommonLib.ComMon;


namespace CommonLib.OfficeReport
{
    public class OfficeReportDLL
    {
        private static string[] SQLSysParameter = new string[9]{ "[当前工作月份]", "[当前时间]", "[当前年]", "[当前月]", "[当前日]","[去年]", 
                                "[今年一月份]", "[今年十二月份]" ,"[去年同期]"};
        private static string WorkingMonth;
        public OfficeReportDLL()
        {
            //获取当前工作月份
            //DataTable WorkingMonth = CommonLib.BLL.sys_p
            WorkingMonth = "201007";
            /*SQLSysParameter = new SQLSysParameter[8];
            SQLSysParameter[0] = "[当前工作月份]";
            SQLSysParameter[1] = "[当前时间]";
            SQLSysParameter[2] = "[当前年]";
            SQLSysParameter[3] = "[当前月]";
            SQLSysParameter[4] = "[当前日]";
            SQLSysParameter[5] = "[去年]";
            SQLSysParameter[6] = "[今年一月份]";
            SQLSysParameter[7] = "[今年十二月份]";
            SQLSysParameter[8] = "[去年同期]";*/
        }

        public bool MSOfficeForMis(string cnstr, Int64 Report_id, string ParaName, string ParaValue, string OutputFile, string AttachFilePath)
        {
            bool result_bl = false;
            System.Data.DataTable dt_template = CommonLib.BLL.UR_REPORTTEMPLATE.GetList("Template_id =" + Report_id.ToString(), "").Tables[0];
            if (dt_template.Rows.Count == 0)
                return false;
            string filepath = AttachFilePath + "\\" + dt_template.Rows[0]["file_content"].ToString();
            //if (!System.IO.Directory())
            if (!File.Exists(filepath))
            {
                if (dt_template.Rows[0]["Template"].ToString() == "")
                {
                    //自定义报表没有模板文件！
                    return false;
                }
                //dt_template.Rows[0]["Template"].GetType();
                byte[] mydata = (byte[])dt_template.Rows[0]["Template"];
                
                int arrayesize = new int();
                arrayesize = mydata.GetUpperBound(0);

                FileStream fs = new FileStream(OutputFile,FileMode.OpenOrCreate, FileAccess.ReadWrite);
                fs.Write(mydata, 0, arrayesize);
                fs.Close();
                /*try
                {
                    FileOutputStream outStream = new FileOutputStream(OutputFile);
                    InputStream inStream = imgBlob.getBinaryStream();
                    byte[] buf = new byte[10240];
                    int len;
                    while ((len = inStream.read(buf)) > 0)
                    {
                        outStream.write(buf, 0, len);
                    }
                    inStream.close();
                    outStream.close();
                }
                catch (Exception e)
                {
                    e.printStackTrace();
                }*/

            }
            else
            {
                File.Copy(filepath,OutputFile, true);
            }

            try
            {
                string[,] DataPara = GetT2WString(ParaName, ParaValue);
                
                int tmp_type = int.Parse(dt_template.Rows[0]["Template_Type"].ToString());
                if (tmp_type == 1 || tmp_type == 3)
                {
                    //word类型的报表

                    GenerateWordReport(Report_id, DataPara, OutputFile, AttachFilePath);
                }
                else 
                {
                    //EXCEL类型的报表

                    GenerateExcelReport(Report_id, DataPara, OutputFile, AttachFilePath);

                }
                result_bl = true;
            }
            catch
            {
            }
            return result_bl;
        }

        public static string[,] GetT2WString(string ParaName, string ParaValue)
        {
            string[,] arrResult;
            if (ParaName.ToString() == "")
                return null;
            try
            {
                string[] sname = ParaName.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                string[] svalue = ParaValue.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                int strlen = sname.Length;
                arrResult =new string[strlen, 2];
                for (int i = 0; i < strlen; i++)
                {
                    arrResult[i, 0] = sname[i];
                    arrResult[i, 1] = svalue[i];
                }
                ////有修改

                /*
                 arrResult =new string[2, strlen];
                for (int i = 0; i < strlen; i++)
                {
                    arrResult[0,i] = sname[i];
                    arrResult[1, i] = svalue[i];
                }
                */

            }
            finally
            {

            }
            return arrResult;
        }

        public static string GetFieldValue(System.Data.DataTable qry_data, string field, int Row)
        {
            string result_s = "";
            if ( field ==null || field =="")//需要的行不存在
                return result_s;
            if (qry_data == null)
                return result_s;
            if (qry_data.Rows.Count <= Row )
                return result_s;
            result_s = qry_data.Rows[Row][field].ToString();
            return result_s;
        }

        public static bool GenerateWordReport(Int64 Report_id, string[,] DataParam, string OutputFile, string AttachFilePath)
        {
            bool result = false;
            object ofile = OutputFile;// WordFile;

            int PreData_id = -1;
            string dataset_id, sqlText;
            string oldText, newText;
            object MissingValue = System.Reflection.Missing.Value;
            Word.Bookmark BM = null;
            object FindText, ReplaceWith, Replace;
            //创建WORK文档
            Word.Application WordApp = new Word.ApplicationClass();
            WordApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone; //避免WORD所有的警告信息
            Word.Document WordDoc = WordApp.Documents.Open(ref ofile,
                                                           ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue,
                                                           ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue,
                                                           ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue);

            Replace = Word.WdReplace.wdReplaceAll;/**//*wdReplaceAll - 替换找到的所有项。

                                                       * wdReplaceNone - 不替换找到的任何项。

                                                       * wdReplaceOne - 替换找到的第一项。

                                                       * */
            //this.WordDoc.Content.Find.ClearFormatting();//移除Find的搜索文本和段落格式设置
            try
            {
                ReplaceWordReportContent(WordDoc, Report_id, DataParam, AttachFilePath);
                System.Data.DataTable tb_params = CommonLib.BLL.UR_REPORTPARAMS.GetList("parameter_type<>3 and template_id ="+Report_id.ToString(), "dataset_id,datarow").Tables[0];
                System.Data.DataTable tb_dataset = CommonLib.BLL.BS_DATASET_SQL.GetList("", "").Tables[0];
                System.Data.DataTable qry_data = null;
                System.Data.DataTable tmpTable = null;
                DataRow[] dr_dataset =null;
                DataRow[] dr_params =null;
                for (int i = 0; i < tb_params.Rows.Count; i++)
                {
                    dataset_id = tb_params.Rows[i]["dataset_id"].ToString();
                    if (dataset_id != null && dataset_id != "" && int.Parse(dataset_id) != 0 && int.Parse(dataset_id) != PreData_id)
                    {
                        dr_dataset = tb_dataset.Select("dataset_id =" + dataset_id);
                        if (dr_dataset.Length != 0)
                        {
                            sqlText = dr_dataset[0]["dataset_sql"].ToString();
                            sqlText = ReplaceSysParameter(sqlText);

                            sqlText = ReplaceDataSqlParam(Int64.Parse(dataset_id), sqlText, DataParam);
                            //SqlOpen(qry_data, sqlText);
                            qry_data = CommonLib.BLL.BS_DATASET_SQL.SqlGetTable(sqlText);
                            PreData_id = int.Parse(dataset_id);
                        }
                        oldText = tb_params.Rows[i]["T_Param_Name"].ToString();
                        newText = GetExpressValue(qry_data,tb_params.Rows[i]["Field_Name"].ToString(), int.Parse(tb_params.Rows[i]["datarow"].ToString()));
                        newText = GetReplaceText(oldText, newText, int.Parse(tb_params.Rows[i]["replace_len"].ToString()), int.Parse(tb_params.Rows[i]["align_mode"].ToString()));
                        //替换文本项

                        if (int.Parse(tb_params.Rows[i]["parameter_type"].ToString()) == 0)
                        {
                            //搜索文本并替换

                            if (oldText.Substring(2, 1).ToUpper() != "P")//不是图片替换
                            {
                                FindText = oldText;//要查找的文本
                                ReplaceWith = newText;//替换文本
                                WordDoc.Content.Find.Text = oldText;
                                WordDoc.Content.Find.Replacement.Text = newText;
                                WordDoc.Content.Find.MatchWildcards = true;
                                WordDoc.Content.Find.Forward = true;
                                WordDoc.Content.Find.Wrap = 0;
                                WordDoc.Content.Find.Format = false;
                                WordDoc.Content.Find.MatchCase = false;
                                WordDoc.Content.Find.MatchWholeWord = false;
                                WordDoc.Content.Find.MatchByte = true;
                                WordDoc.Content.Find.MatchSoundsLike = false;
                                WordDoc.Content.Find.MatchAllWordForms = false;
                                WordDoc.Content.Find.Execute(
                                            ref FindText, ref MissingValue,
                                            ref MissingValue, ref MissingValue,
                                            ref MissingValue, ref MissingValue,
                                            ref MissingValue, ref MissingValue, ref MissingValue,
                                            ref ReplaceWith, ref Replace,
                                            ref MissingValue, ref MissingValue,
                                            ref MissingValue, ref MissingValue);
                                InsetLogTable("替换标志字-" + oldText + " -> " + newText);
                            }
                            else //'P' then  是图片替换

                            {

                            }
                        }
                        //书签
                        else if (int.Parse(tb_params.Rows[i]["parameter_type"].ToString()) == 1)
                        {
                            if (WordDoc.Bookmarks.Exists(oldText))
                            {
                                for (int ii = 0; ii < WordDoc.Bookmarks.Count; ii++)
                                {
                                    FindText = oldText;
                                    BM = WordDoc.Bookmarks.get_Item(ref FindText);
                                    BM.Select();
                                    oldText = BM.Range.Text;
                                    newText = StringCommon.FillEqualLengthString(oldText, newText, ' ', 1);
                                    BM.Range.Text = newText;
                                    InsetLogTable("替换书签-" + oldText + " -> " + newText);

                                }
                            }
                        }
                    }
                }
                tb_params = CommonLib.BLL.UR_REPORTPARAMS.GetList("parameter_type =3 and template_id =" + Report_id.ToString(), "dataset_id,datarow").Tables[0];
                if (tb_params.Rows.Count > 0)
                {
                    PreData_id = -1;
                    foreach (Word.Table wdTable in WordDoc.Tables)
                    {
                        int[, ,] maxcell = new int[wdTable.Rows.Count + 1, wdTable.Columns.Count + 1, 2];
                        for (int j = 1; j <= wdTable.Rows.Count; j++)////有修改j < wdTable.Rows.Count
                        {
                            for (int k = 1; k <= wdTable.Columns.Count; k++)
                            {
                                maxcell[j, k, 0] = j > 0 ? maxcell[j - 1, k, 0] + 1 : j;
                                maxcell[j, k, 1] = k > 1 ? maxcell[j, k - 1, 1] + 1 : k;
                                //为解决有的表格进行了单元格合并，使读取发生异常

                                try { oldText = wdTable.Cell(maxcell[j, k, 0], maxcell[j, k, 1]).Range.Text; }
                                catch { oldText = ""; }
                                int i = 0;
                                dr_params = tb_params.Select("T_Param_Name='" + CommonLib.ComMon.StringCommon.TrimESCChar(oldText) + "'");
                                if (dr_params.Length > 0)
                                {
                                    InsetLogTable("处理表格项－" + oldText);
                                    //打开数据集


                                    if (PreData_id != int.Parse(dr_params[0]["dataset_id"].ToString())) //需要获取数据集
                                    {
                                        PreData_id = int.Parse(dr_params[0]["dataset_id"].ToString());
                                        tmpTable = CommonLib.BLL.BS_DATASET_SQL.GetDataSetBySqlParam(PreData_id, DataParam);
                                        //dr_dataset = tb_dataset.Select(" dataset_id =" + dr_params[0]["dataset_id"].ToString());

                                        PreData_id = int.Parse(dr_params[0]["dataset_id"].ToString());
                                    }
                                    switch (int.Parse(dr_params[0]["repeat_type"].ToString()))
                                    {
                                        case 0: //不循环

                                            newText = DataCommon.GetDataTableCell(tmpTable, int.Parse(dr_params[0]["datarow"].ToString()),
                                                           dr_params[0]["Field_Name"].ToString()).ToString();
                                            wdTable.Cell(maxcell[j, k, 0], maxcell[j, k, 1]).Range.Text = newText;
                                            break;
                                        case 1://行循环

                                            for ( i = 0; i < int.Parse(dr_params[0]["repeat_num"].ToString()) || int.Parse(dr_params[0]["datarow"].ToString()) + i < tmpTable.Rows.Count; i++)
                                            {
                                                //在固定的可循环的行范围内
                                                if (i < int.Parse(dr_params[0]["fix_reprat_num"].ToString()))
                                                //for(i = 0;i < int.Parse(dr_params[0]["fix_reprat_num"].ToString());i++)
                                                {
                                                    newText = DataCommon.GetDataTableCell(tmpTable, int.Parse(dr_params[0]["datarow"].ToString()) + i,
                                                                   dr_params[0]["Field_Name"].ToString()).ToString();
                                                    wdTable.Cell(maxcell[j, k, 0] + i, maxcell[j, k, 1]).Range.Text = newText;
                                                }
                                                //超出了固定的可循环的行范围，out_operate＝0停止循环，out_operate＝1，插入表格的方式继续循环
                                                else if (int.Parse(dr_params[0]["out_operate"].ToString()) > 0)
                                                //if (tmpTable.Rows.Count > int.Parse(dr_params[0]["fix_reprat_num"].ToString()) && int.Parse(dr_params[0]["out_operate"].ToString()) >0)
                                                {
                                                    //while (i < tmpTable.Rows.Count)
                                                    object beforeRow = wdTable.Rows[maxcell[j, k, 0] + i];
                                                    wdTable.Rows.Add(ref beforeRow);
                                                    newText = DataCommon.GetDataTableCell(tmpTable, int.Parse(dr_params[0]["datarow"].ToString()) + i,
                                                                   dr_params[0]["Field_Name"].ToString()).ToString();
                                                    wdTable.Cell(maxcell[j, k, 0] + i, maxcell[j, k, 1]).Range.Text = newText;
                                                    maxcell[j, k, 0] = maxcell[j, k, 0] + i;
                                                }
                                            }
                                            break;
                                        case 2: //列循环

                                            for (i = 0; i < int.Parse(dr_params[0]["repeat_num"].ToString()) || int.Parse(dr_params[0]["datarow"].ToString()) + i < tmpTable.Rows.Count; i++)
                                            {
                                                //在固定的可循环的行范围内
                                                if (i < int.Parse(dr_params[0]["fix_reprat_num"].ToString()))
                                                {
                                                    newText = DataCommon.GetDataTableCell(tmpTable, int.Parse(dr_params[0]["datarow"].ToString()) + i,
                                                                   dr_params[0]["Field_Name"].ToString()).ToString();
                                                    wdTable.Cell(maxcell[j, k, 0], maxcell[j, k, 1] + i).Range.Text = newText;
                                                }
                                                //超出了固定的可循环的行范围，out_operate＝0停止循环，out_operate＝1，插入表格的方式继续循环
                                                else if (int.Parse(dr_params[0]["out_operate"].ToString()) > 0)
                                                {
                                                    object beforeCol = wdTable.Columns[maxcell[j, k, 1] + i];
                                                    wdTable.Columns.Add(ref beforeCol);
                                                    newText = DataCommon.GetDataTableCell(tmpTable, int.Parse(dr_params[0]["datarow"].ToString()) + i,
                                                                   dr_params[0]["Field_Name"].ToString()).ToString();
                                                    wdTable.Cell(maxcell[j, k, 0], maxcell[j, k, 1] + i).Range.Text = newText;
                                                    maxcell[j, k, 0] = maxcell[j, k, 1] + i;
                                                }
                                            }
                                            break;
                                        default:
                                            break;
                                    }

                                }
                            }
                        }
                    }
                }

            }
            catch(System.Exception ex)
            {
                throw ex;
                result = false;
            }
            finally
            {
                //执行结束，不管是否发生异常，文件保存
                WordDoc.SaveAs(ref ofile, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue, ref MissingValue);
                WordDoc.Close(ref MissingValue, ref MissingValue, ref MissingValue);
                //最后退出word
                WordApp.Quit(ref MissingValue, ref MissingValue, ref MissingValue);
            }
            return result;
        }

        public static bool ReplaceWordReportContent(Word.Document WordDoc, Int64 Report_id, string[,] DataParam, string AttachFilePath)
        {
            //获取当前工作月份
            //DataTable WorkingMonth = CommonLib.BLL.sys_p
            return true;
        }

        public static string ReplaceSysParameter(string sqlstr)
        {
            string result = sqlstr;
            for (int i = 0; i < SQLSysParameter.Length; i++)
            {
                result = result.Replace(SQLSysParameter[i], getSysSQlParameter(SQLSysParameter[i]));
            }
            return result;
        }

        public static string getSysSQlParameter(string parameterid)
        {
            string result = "";
            string workmonth = WorkingMonth;
            switch (parameterid)
            {
                case "[当前工作月份]":
                    result = workmonth;
                    break;
                case "[当前时间]":
                    result = DateTime.Now.ToString();
                    break;
                case "[当前年]":
                    result = DateTime.Today.Year.ToString();
                    break;
                case "[当前月]":
                    result = DateTime.Today.Month.ToString();
                    break;
                case "[当前日]":
                    result =DateTime.Today.ToString("dd"); 
                    break;
                case "[去年]":
                    result = DateTime.Today.AddYears(-1).ToString("yyyy-MM-dd");
                    break;
                case "[今年一月份]":
                    result = workmonth.Substring(0, 4).ToString() + "01";
                    break;
                case "[今年十二月份]":
                    result = workmonth.Substring(0, 4).ToString() + "12";
                    break;
                case "[去年同期]":
                    result = Convert.ToString(int.Parse(workmonth.Substring(0, 4)) - 1) + workmonth.Substring(4,2).ToString();
                    break;
                default:
                    result ="";
                    break;
          
            }
            return result;
        }

        public static string ReplaceDataSqlParam(Int64 dataset_id, string sqlText, string[,] DataParam)
        {
            string result = sqlText;
            System.Data.DataTable tb_dataset_param = CommonLib.BLL.BS_DATASET_PARAMETER.GetList("dataset_id = " + dataset_id.ToString(),"").Tables[0];
            for (int i = 0;i < tb_dataset_param.Rows.Count; i++)
            {
                if (DataParam != null)
                {
                    for (int ii = 0; ii < DataParam.GetLength(1); ii++)
                    {
                        if (DataParam[ii, 0] == tb_dataset_param.Rows[i]["parameter_name"].ToString())
                        {
                            result = result.Replace(tb_dataset_param.Rows[i]["parameter_name"].ToString(), DataParam[ii, 1]);
                            break;
                        }
                    }
                }
                else
                {
                    result = result.Replace(tb_dataset_param.Rows[i]["parameter_name"].ToString(), tb_dataset_param.Rows[i]["parameter_default"].ToString());
                }
            }
            return result;
        }

        public static string GetReplaceText(string oldText, string newText, int replace_len, int Align_mode)
        {
            string result_s = "";
            if (replace_len == 0)
                result_s = GetReplaceText(newText, oldText.Length, Align_mode); 
            else
                result_s = GetReplaceText(newText, replace_len, Align_mode);
            return result_s;

        }

        public static string GetReplaceText(string newText, int replace_len, int Align_mode)
        {
            string result_s = "";
            int bu_len = replace_len - newText.Length; 
            //if (bu_len <= 0)
            //    bu_len = 0;
            bu_len = bu_len > 0 ? bu_len : 0;

            switch (Align_mode)
            {
                case 0:
                    result_s = newText.PadRight(bu_len,Convert.ToChar(" ")).ToString();
                    break;
                case 1:
                    result_s = newText.PadLeft(Convert.ToInt32(bu_len / 2), Convert.ToChar(" ")).PadRight(bu_len - Convert.ToInt32(bu_len / 2), Convert.ToChar(" ")).ToString();
                    break;
                case 2:
                    result_s = newText.PadLeft(bu_len, Convert.ToChar(" ")).ToString();
                    break;
            }
            return result_s;

        }

        public static string GetExpressValue(System.Data.DataTable qry_data,string Express,int Row)
        {
            string result ="";
            string fieldname,newSql;
            int qstart, qend;
            if (Express.ToString() == "")
                return "";
            if (Express.Substring(0, 1) != "=")
            {
                result = GetFieldValue(qry_data, Express, Row);
            }
            else
            {
                while (true)
                {
                    qstart = Express.IndexOf("[", 0);
                    qend = 0;/////
                    if (qstart > 0)
                    {
                        qend = Convert.ToString(Express + qstart.ToString()).IndexOf("]",0);
                        if (qend > 0)
                        {
                            fieldname = Express.Substring(qstart + 1, qend - qstart - 1);
                            newSql = Express.Replace("[" + fieldname + "]", GetFieldValue(qry_data, fieldname, Row));
                        }
                    }
                    if (qstart <= 0 && qend <= 0)
                        break;
                }
                System.Data.DataTable qry_express =null;//= CommonLib.BLL.' select ' + string(pchar(Express) + 1);
                qry_express = CommonLib.BLL.BS_DATASET_SQL.SqlGetTable("select " + Express.ToString()+"1");
                if (qry_express.Rows.Count > 0)
                {
                    result = qry_express.Rows[0][0].ToString();

                }
            }
            return result;
        }

        public static int InsetLogTable(string s)
        {
            //仅做调试用，需建表create table tmplog (logtime datetime,log varchar(1000))
            //DBUtility.dbCommon.ExecSqlCommand("insert into tmplog values('"+System.DateTime.Now.ToString()+"','"+s+"')");
            return 0;
        }

        //////////////////////////////////////////////////////////////////////////////////////////////////
        ///////////////以下内容为调用EXCEL类型
        #region 以下内容为调用EXCEL类型
        public static bool GenerateExcelReport(Int64 Report_id,string[,] DataParam,string OutputFile, string AttachFilePath)
        {
            bool result = false;
            object MissingValue = System.Reflection.Missing.Value;
            string ofile = OutputFile;
            string cellText, cValue, sqlText;

            //获取当前工作月份
            //DataTable WorkingMonth = CommonLib.BLL.sys_p
            int PreData_id = -1;
            string dataset_id;
            //创建EXCEL文档
           
            Excel.Application xlApp = new Excel.Application();
            if(xlApp==null)
            {
            //MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
            return false;
            }
            
            Excel.Workbooks workbooks=xlApp.Workbooks;
            //Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Workbook workbook = workbooks.Open(ofile,
                                                           MissingValue, MissingValue,  MissingValue,  MissingValue,  MissingValue,
                                                           MissingValue,  MissingValue,  MissingValue,  MissingValue, MissingValue,
                                                           MissingValue,  MissingValue,  MissingValue,  MissingValue);
            Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            Excel.Range range ;//= worksheet.UsedRange;
            
            
            /*
            Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook xBook = null;
            xApp.Visible = false;
            xBook = xApp.Workbooks._Open(ofile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];
            Microsoft.Office.Interop.Excel.Range cell = (Microsoft.Office.Interop.Excel.Range)xSheet.Cells[2, 1];
            */
            try
            {
                int MaxRow = worksheet.UsedRange.Rows.Count;
                int MaxCol = worksheet.UsedRange.Columns.Count;
                int[,] CellNewPosX = new int[MaxRow + 1,MaxCol + 1];
                int[,] CellNewPosY = new int[MaxRow + 1,MaxCol + 1];
                System.Data.DataTable tb_params = CommonLib.BLL.UR_REPORTPARAMS.GetList(" template_id =" + Report_id.ToString(), "dataset_id,datarow").Tables[0];////有修改"parameter_type<>3"
                System.Data.DataTable tb_dataset = CommonLib.BLL.BS_DATASET_SQL.GetList("", "").Tables[0];
                System.Data.DataTable qry_data = null;
                DataRow[] dr_params = null;
                DataRow[] dr_dataset = null;
                //对表格，按表格的行列进行循环
                for (int i = 1;i <=MaxRow;i++ )
                {
                    for (int j = 1;j <= MaxCol ; j++)
                    {
                        if (i > 1)
                        {
                            CellNewPosX[i,j] = CellNewPosX[i - 1,j] + 1;
                        }
                        else
                            CellNewPosX[i,j] = 1;
                        if (j >1)
                            CellNewPosY[i,j] = CellNewPosY[i,j - 1] + 1;
                        else
                            CellNewPosY[i,j] = 1;
                    
                        int a1 = CellNewPosX[i, j];
                        int a2 = CellNewPosY[i, j];
                        //range = (Excel.Range)worksheet.UsedRange.Cells[CellNewPosX[i, j], CellNewPosY[i, j]];
                        range = (Excel.Range)worksheet.UsedRange.Cells[a1, a2];
                        //cellText = range.get_Value(MissingValue).ToString();//.Value2.ToString();//Cells[a1, a2].ToString();// range.ToString(); 
                        
                        //////此处加改语句的作用是有合并单元格的情况会报错
                        try
                        {
                            cellText = range.get_Value(MissingValue).ToString();
                        }
                        catch
                        {
                            continue;
                        }
                        cellText = cellText.Replace("\r", "").Replace("\a", "").Replace("\n", "");
                        //cellText.Replace("\n", ""); 
                        if (cellText.Substring(0, 1) == "〔" && cellText.Substring(cellText.Length - 1, 1) == "〕")
                        {
                            dr_params = tb_params.Select("T_Param_Name = '" + cellText + "'");
                            if (dr_params.Length != 0)
                            {
                                dataset_id = dr_params[0]["dataset_id"].ToString();
                                if (dataset_id != null && dataset_id != "" && int.Parse(dataset_id) != 0 && int.Parse(dataset_id) != PreData_id)
                                {
                                    dr_dataset = tb_dataset.Select("dataset_id =" + dataset_id);
                                    if (dr_dataset.Length != 0)
                                    {
                                        sqlText = dr_dataset[0]["dataset_sql"].ToString();
                                        sqlText = ReplaceSysParameter(sqlText);

                                        sqlText = ReplaceDataSqlParam(Int64.Parse(dataset_id), sqlText, DataParam);
                                        //SqlOpen(qry_data, sqlText);
                                        qry_data = CommonLib.BLL.BS_DATASET_SQL.SqlGetTable(sqlText);

                                        PreData_id = int.Parse(dataset_id);
                                    }
                                }
                                    int fix_reprat_num;
                                    switch (int.Parse(dr_params[0]["repeat_type"].ToString()))
                                    {
                                        case 0:
                                            cValue = GetExpressValue(qry_data, dr_params[0]["Field_Name"].ToString(), int.Parse(dr_params[0]["datarow"].ToString()));
                                            range.Value2 = cValue;
                                            break;
                                        case 1://在固定的可循环的行范围内
                                            for (int ii = 0; ii < int.Parse(dr_params[0]["fix_reprat_num"].ToString()) - 1; ii++)
                                            {
                                                range = (Excel.Range)worksheet.UsedRange.Cells[CellNewPosX[i, j] + ii, CellNewPosY[i, j]];
                                                cValue = GetExpressValue(qry_data, dr_params[0]["Field_Name"].ToString(), int.Parse(dr_params[0]["datarow"].ToString()) + ii);
                                                range.Value2 = cValue;
                                            }
                                            //超出了固定的可循环的行范围，out_operate＝0停止循环，out_operate＝1，插入表格的方式继续循环
                                            fix_reprat_num = int.Parse(dr_params[0]["fix_reprat_num"].ToString());
                                            if (qry_data.Rows.Count > int.Parse(dr_params[0]["fix_reprat_num"].ToString()) && int.Parse(dr_params[0]["out_operate"].ToString()) > 0)
                                            {
                                                while (fix_reprat_num < qry_data.Rows.Count)
                                                {
                                                    range = (Excel.Range)worksheet.UsedRange.Cells[CellNewPosX[i, j] + fix_reprat_num, CellNewPosY[i, j]];
                                                    cValue = GetExpressValue(qry_data, dr_params[0]["Field_Name"].ToString(), int.Parse(dr_params[0]["datarow"].ToString()) + fix_reprat_num);
                                                    range.Value2 = cValue;
                                                    fix_reprat_num++;
                                                }
                                            }
                                           /* if (qry_data.Rows.Count == 0)
                                            {
                                                range = (Excel.Range)worksheet.UsedRange.Cells[CellNewPosX[i, j], CellNewPosY[i, j]];
                                                //cValue = GetExpressValue(qry_data, dr_params[0]["Field_Name"].ToString(), int.Parse(dr_params[0]["datarow"].ToString()) + ii);
                                                range.Value2 = "";
                                            }*/
                                            CellNewPosX[i, j] = CellNewPosX[i, j] + fix_reprat_num - 1;
                                            break;
                                        case 2://列循环

                                            for (int ii = 0; ii < int.Parse(dr_params[0]["fix_reprat_num"].ToString()) - 1; ii++)
                                            {
                                                range = (Excel.Range)worksheet.UsedRange.Cells[CellNewPosX[i, j] + ii, CellNewPosY[i, j]];
                                                cValue = GetExpressValue(qry_data, dr_params[0]["Field_Name"].ToString(), int.Parse(dr_params[0]["datarow"].ToString()) + ii);
                                                range.Value2 = cValue;
                                            }
                                            //超出了固定的可循环的行范围，out_operate＝0停止循环，out_operate＝1，插入表格的方式继续循环
                                            fix_reprat_num = int.Parse(dr_params[0]["fix_reprat_num"].ToString());
                                            if (qry_data.Rows.Count > int.Parse(dr_params[0]["fix_reprat_num"].ToString()) && int.Parse(dr_params[0]["out_operate"].ToString()) > 0)
                                            {
                                                while (fix_reprat_num < qry_data.Rows.Count)
                                                {
                                                    //range = worksheet.UsedRange.Cells[CellNewPosX[i, j] + fix_reprat_num, CellNewPosY[i, j]];
                                                    cValue = GetExpressValue(qry_data, dr_params[0]["Field_Name"].ToString(), int.Parse(dr_params[0]["datarow"].ToString()) + fix_reprat_num);
                                                    //range.Value2 = cValue;
                                                    fix_reprat_num++;
                                                }
                                            }
                                           /* if (qry_data.Rows.Count == 0)
                                            {
                                                range = (Excel.Range)worksheet.UsedRange.Cells[CellNewPosX[i, j], CellNewPosY[i, j]];
                                                //cValue = GetExpressValue(qry_data, dr_params[0]["Field_Name"].ToString(), int.Parse(dr_params[0]["datarow"].ToString()) + ii);
                                                range.Value2 = "";
                                            }*/
                                            CellNewPosX[i, j] = CellNewPosX[i, j] + fix_reprat_num - 1;
                                            break;

                                    }

                               // }
                            }
                            //else
                            //    break;
                        }
                    }
                    
                }
                result = true;
            }
            catch(System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                xlApp.DisplayAlerts = false;
                worksheet.SaveAs(ofile,  MissingValue,MissingValue, MissingValue, MissingValue, Excel.XlSaveAsAccessMode.xlNoChange,
                                 MissingValue, MissingValue, MissingValue, MissingValue);
                //xlApp.Application.ActiveWorkbook.SaveAs(ofile,  MissingValue,MissingValue, MissingValue,  MissingValue,  MissingValue, Excel.XlSaveAsAccessMode.xlNoChange,// MissingValue,
                //                  MissingValue, MissingValue, MissingValue, MissingValue, MissingValue);
                //workbook.Close(false,MissingValue,false);
                workbooks.Close();
                workbooks.Application.Quit();
            }
            return result;
        }
        #endregion

    }

}
